﻿using System;
using System.Text;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using bzxxxt.admin.service;
using bzxxxt.dal;
using NPOI.SS.UserModel;
using bzxxxt.model;
using NPOI.SS.Formula.Eval;

namespace bzxxxt.bll
{
    public class ImportBLL:BaseBLL
    {
        private string oldConnStr = ConfigurationManager.ConnectionStrings["olddbconn"].ConnectionString;
        private string newConnStr = ConfigurationManager.ConnectionStrings["newconn"].ConnectionString;
        admin.entity.UserEntity user = admin.service.UserService.GetUser(HttpContext.Current);   //获取cookie里的用户对象
        
        public ImportBLL() {
            string path = HttpContext.Current.Server.MapPath("/admin/upload/log.txt");
            StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create), System.Text.Encoding.UTF8);
            sw.Close();
        }
        private void Log(string msg)
        {
            string path = HttpContext.Current.Server.MapPath("/admin/upload/log.txt");
            StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create), System.Text.Encoding.UTF8);
            sw.WriteLine(string.Format("{0:yyyyMMdd HH:mm:ss}: {1}", DateTime.Now, msg));
            sw.Close();
            HttpContext.Current.Response.Write(msg+"<br/>");
            HttpContext.Current.Response.Flush();
        }

        public DataTable GetParadiseList() {
            string sql = string.Format("select distinct othername from vipclass where vipclass=2");
            SqlDataAdapter da = new SqlDataAdapter(sql, oldConnStr);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public string ExportParadiseData(string pname,  DateTime? dtt = null)
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();
            string mapFile = HttpContext.Current.Server.MapPath("/admin/upload/cunmapdata.txt");
            StreamReader sr = new StreamReader(mapFile);
            string line = sr.ReadLine();
            while (line != null) {
                string[] data = line.Split(new string[] { " ", "\t" }, StringSplitOptions.RemoveEmptyEntries);
                dict.Add(data[0].Trim(), data[1].Trim());
                line = sr.ReadLine();
            }

            if (dtt == null) dtt = DateTime.MinValue;

            pname = pname.Trim();
            string sql = string.Format("select 1 as 新栋,null as 新楼层, null as 新区, null as 新排, null as 新层, null as 新列, z.位置 as 原位置,z.骨灰堂名,r.穴状,r.骨灰盒数,r.登记日期,r.联系姓名,r.性别,r.联系电话,r.村居委,r.所在镇,r.家庭住址,r.身份证号," +
                        "z.亡故者姓名,z.与联系者关系,z.骨灰类型,z.存放日期,r.区,r.排,r.号,z.id as 死者ID" +
                        " from lianxi r,zang z where r.位置=z.位置  and z.骨灰堂名='{0}' and  z.存放日期 > '{1:yyyy-MM-dd}'", pname,dtt);
            SqlDataAdapter da = new SqlDataAdapter(sql, oldConnStr);
            DataTable dt = new DataTable();
            da.Fill(dt);
            foreach (DataRow dr in dt.Rows) {
                string cun = dr["村居委"].ToString().Trim();
                if (dict.ContainsKey(cun)) { 
                    string newcun = "";
                    dict.TryGetValue(cun,out newcun);
                    dr["村居委"] = newcun;
                }
            }
            //string sqltrans = string.Format("select id, oldsection,dong,floor,section,oldline,line,oldnum layer,col from tbTransform", pname);
            //SqlDataAdapter da2 = new SqlDataAdapter(sql, newConnStr);
            //DataTable dtTrans = new DataTable();
            //da2.Fill(dtTrans);
            //foreach (DataRow dr in dt.Rows) {
            //    string oldsection = dr["区"].ToString().Trim();
            //    string oldline = dr["排"].ToString().Trim();
            //    string oldnum = dr["号"].ToString().Trim();
            //    DataRow tr  =dtTrans.Select(string.Format("oldsection='{0}'",oldsection)).FirstOrDefault();
            //    if (tr != null)
            //    {
            //        dr["新栋"] = tr["dong"];
            //        dr["新楼层"] = tr["floor"];
            //        dr["新区"] = tr["section"];
            //        string oline = tr["oldline"].ToString();
            //        if (string.IsNullOrEmpty(oline)) dr["新排"] = oldline;

            //        tr = dtTrans.Select(string.Format("oldline='{0}'", oldline)).FirstOrDefault();
            //        if (tr != null)
            //        {
            //            dr["排"] = tr["line"];
            //        }
            //        tr = dtTrans.Select(string.Format("oldnum LIKE '{0}'", oldnum)).FirstOrDefault();
            //        if (tr != null)
            //        {
            //            dr["新层"] = tr["layer"];
            //        }
            //    }
            //}
            string fname = "/admin/upload/" + pname + ".xls";
            string f = HttpContext.Current.Server.MapPath(fname);
            NPOIHelper.ExportDTtoExcel(dt, pname +"【"+dt.Rows.Count +  "】个死者", f);
            return fname;
        }

        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="sheet">需要导出的sheet序号</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <returns></returns>
        public List<ImportModel> ImportExceltoDb(string strFileName, int SheetIndex, int HeaderRowIndex)
        {
            List<ImportModel> list = new List<ImportModel>();
            IWorkbook wb;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                wb = WorkbookFactory.Create(file);
            }
            ISheet sheet = wb.GetSheetAt(SheetIndex);

            IRow headerRow;
            int cellCount;
            StringBuilder sbMsg = new StringBuilder();
            try
            {
                //检查表格的标题是否符合模板格式

                headerRow = sheet.GetRow(HeaderRowIndex);
                cellCount = headerRow.LastCellNum;
                string[] headers = { "新栋", "新楼层", "新区", "新排", "新层", "新列", "原位置", "骨灰堂名", "穴状", "骨灰盒数", "登记日期", "联系姓名",
                                   "性别","联系电话","村居委","所在镇","家庭住址","身份证号","亡故者姓名","与联系者关系","骨灰类型","存放日期","区","排","号"};
                //新栋	新楼层	新区	新排	新层	新列	原位置	骨灰堂名	穴状	骨灰盒数	登记日期	联系姓名	
                //性别	联系电话	村居委	所在镇	家庭住址	身份证号	亡故者姓名	与联系者关系	骨灰类型	存放日期	区	排	号
																
                if (cellCount != headers.Length)
                {
                    throw new Exception("数据文件格式列数与模板列数不符！");
                }
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    string headtitle = headerRow.GetCell(i).ToString();
                    if (headers[i] != headtitle)
                    {
                        throw new Exception("数据文件第" + i + "列名称与数据模板不符！");
                    }
                }
                int rowCount = sheet.LastRowNum;

                StringBuilder sbLine = new StringBuilder();
                for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
                {
                    if (!Cfg.IsRunning) break;
                    ImportModel m = new ImportModel();
                    sbLine.Clear();
                    try
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null)
                        {
                            sbMsg.AppendFormat("第{0}行无数据!<br/>", i + 1);
                            continue;
                        }
                        m.id = i;
                        
                        //新栋
                        string dong = getCellVaue(row.GetCell(0));
                        m.dong = int.Parse(dong);
                        //新楼层
                        string floor = getCellVaue(row.GetCell(1));
                        m.floor = int.Parse(floor);
                        //新区
                        m.SectionStr = getCellVaue(row.GetCell(2));
                        m.SectionStr = Utils.getSectionStr(m.SectionStr);

                        //新排
                        string line = getCellVaue(row.GetCell(3));
                        m.LineStr = line;
                        m.LineStr = m.LineStr.Substring(m.LineStr.Length - 2);

                        //新层
                        string layer = getCellVaue(row.GetCell(4));
                        m.layer = int.Parse(layer);

                        //5新列
                        string col = getCellVaue(row.GetCell(5));
                        m.col = int.Parse(col);

                        //原位置
                        string oldgrave = getCellVaue(row.GetCell(6));
                        m.oldgrave = oldgrave;

                        //骨灰堂名
                        string paradise = getCellVaue(row.GetCell(7));
                        m.pname = paradise;
                        //处理骨灰堂
                        var p = DB.tbParadise.FirstOrDefault(mp => mp.name == paradise);
                        if (p == null)
                        {
                            string msg = string.Format("第{0}行骨灰堂【{1}】无效! <br/>", i + 1, paradise);
                            throw new Exception(msg);
                        }
                        else
                        {
                            m.pcode = p.code;
                        }

                        
                        //穴状 8
                        string gravetype = getCellVaue(row.GetCell(8));
                        if (gravetype.IndexOf('单') >= 0) m.gtype = 1;
                        else if (gravetype.IndexOf('双') >= 0) m.gtype = 2;
                        else m.gtype = 3;

                        //骨灰盒数
                        string qty = getCellVaue(row.GetCell(9));
                        m.qty = int.Parse(qty);

                        //登记日期
                        string regTime = getCellVaue(row.GetCell(10));
                        if (!string.IsNullOrEmpty(regTime)) m.regTime = DateTime.Parse(regTime);

                        //联系姓名11
                        string rname = getCellVaue(row.GetCell(11));
                        if (string.IsNullOrEmpty(rname))
                        {
                            sbLine.AppendFormat("第{0}行户主姓名不能为空! ", i + 1);
                        }
                        else
                        {
                            m.rname = rname;
                        }
                        

                        //性别
                        string rsex = getCellVaue(row.GetCell(12));
                        m.rsex = rsex;

                        
                        //联系电话
                        string rtel = getCellVaue(row.GetCell(13));
                        m.rtel = rtel;
                        
                        //所在镇15
                        string zheng = getCellVaue(row.GetCell(15));

                        //家庭住址
                        string zu = getCellVaue(row.GetCell(16));
                        m.zu = zu;

                        string cun = getCellVaue(row.GetCell(14));
                        //村居委
                        var c = DB.tbArea.FirstOrDefault(area => area.name.IndexOf(cun)>=0);
                        if (c == null)
                        {
                            sbLine.AppendFormat("第{0}行村别【{1}】无效! ", i + 1, cun);
                            m.cun = null;
                            m.zu = cun + zu;
                        }
                        else
                        {
                            m.cun = c.code;
                        }

                        

                        
                        //身份证号
                        string rcardno = getCellVaue(row.GetCell(17));
                        m.rcardno = rcardno;
                        if (m.rcardno.Length > 18) m.rcardno = m.rcardno.Substring(0, 18);

                        //亡故者姓名
                        string dname = getCellVaue(row.GetCell(18));
                        if (string.IsNullOrEmpty(dname))
                        {
                            sbLine.AppendFormat("第{0}行死者姓名不能为空! <br/>", i + 1);
                        }
                        else
                        {
                            m.dname = dname;
                        }

                        //与联系者关系19
                        string relation = getCellVaue(row.GetCell(19));
                        m.relation = relation;

                        //string[] headers = { "新栋", "新楼层", "新区", "新排", "新层", "新列", "原位置", "骨灰堂名",  "穴状", "骨灰盒数", "登记日期", "联系姓名",
                        //          "性别","联系电话","村居委","所在镇","家庭住址","身份证号","亡故者姓名","与联系者关系","骨灰类型","存放日期"};

                        //骨灰类型
                        string storetype = getCellVaue(row.GetCell(20));
                        m.storeType = storetype;
                        m.dsex = "不详";

                        string storetime = getCellVaue(row.GetCell(21));
                        if (!string.IsNullOrEmpty(storetime)) m.storeTime = DateTime.Parse(storetime);
                       
                        //测试新的存放位置是否存在
                        string loc = m.location;
                        string section = m.SectionStr.Substring(m.SectionStr.Length-2);
                        string locName = string.Format("{1}栋{2}楼{3:00}区{4}排{5:00}层{6:000}号", m.pname, m.dong, m.floor, section, m.LineStr, m.layer, m.col);
                        
                        if (p.type != "骨灰堂") //如果不是骨灰堂
                        {
                            char cc = Convert.ToChar('A' + m.gtype - 1);
                            loc = string.Format("{0:000}{1}{2}{3:00000}{4:0}", m.pcode, Utils.getSectionStr4Bit(m.SectionStr), m.LineStr, m.col, cc);
                            locName = string.Format("{0}区{1}排{2:00000}号", m.SectionStr, m.LineStr, m.col);
                            section = m.SectionStr;
                            m.layer = m.col / 1000 % 100;
                            m.col = m.col % 1000;
                            floor = "0";
                        }
                       
                        //var g = DB.tbGrave.FirstOrDefault(mg => mg.location.Substring(0,mg.location.Length-1) == loc.Substring(0,loc.Length-1));
                        var g = DB.tbGrave.FirstOrDefault(mg=>mg.importloc==m.oldgrave);
                        if (g == null)
                        {
                            sbLine.AppendFormat("第{0}行存放位置{1}不存在!,将新增插入<br/> ", i + 1, loc);
                            GraveBLL gb = new GraveBLL();                                   
                            tbGrave grave = new tbGrave
                            {

                                location = loc,
                                name = locName,

                                hourseNum =  dong,
                                floorNum = floor,
                                sectionNum = section,
                                lineNum = m.LineStr,
                                layerNum =  string.Format("{0:00}",m.layer),
                                columnNum =  string.Format("{0:000}",m.col),
                                qty = 0,
                                importloc = m.oldgrave,
                                graveType = m.gtype                                
                            };
                            
                            var pd = DB.tbParadise.FirstOrDefault(pr => pr.code == m.pcode);
                            grave.paradiseId = pd.id;
                            DB.tbGrave.Add(grave);
                            DB.SaveChanges();

                            m.graveId = grave.id;
                        }
                        else
                        {
                            m.graveId = g.id;
                        }
                        list.Add(m);

                    }
                    catch (Exception ex1)
                    {
                        sbLine.AppendFormat(" 第{0}行数据格式错误!<br/>" + ex1.Message, i + 1);
                    }

                    if (sbLine.Length > 0)
                    {
                        sbMsg.AppendLine(sbLine.ToString() + "<br/>");
                        sbLine.Clear();
                    }
                    if (i % 100 == 0)
                    {
                        HttpContext.Current.Response.Write("[" + i + "]");
                        HttpContext.Current.Response.Flush();
                    }
                }
                HttpContext.Current.Response.Write("ok[" + rowCount + "]<br/>");
                HttpContext.Current.Response.Flush();
                
            }
            catch (Exception ex)
            {
                sbMsg.Append("<br/>"+ex.Message);
            }
            if (sbMsg.Length > 0)
            {
                string path = HttpContext.Current.Server.MapPath("/admin/upload/log.html");
                FileStream fs = new FileStream(path, FileMode.Create);
                StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
                sw.WriteLine("部分数据处理出错:<br/>");
                sw.WriteLine(sbMsg.ToString());
                sw.Close();
                //throw new Exception("/admin/upload/log.html");
            }
            else
            {
                string path = HttpContext.Current.Server.MapPath("/admin/upload/log.html");
                FileStream fs = new FileStream(path, FileMode.Create);
                StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
                sw.WriteLine("数据处理成功，准备导入！<br/>");               
                sw.Close();
            }
            sheet = null;

            GraveBLL gbll = new GraveBLL();
            try
            {
                gbll.Import(list);
            }
            catch (Exception ex) {
                throw new Exception("/admin/upload/log.html");
            }

            return list;
        }

        private string getCellVaue(ICell cell)
        {
            string v = "";
            switch (cell.CellType)
            {
                case CellType.String:
                    v = cell.StringCellValue;

                    break;
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        DateTime dt = DateTime.FromOADate(cell.NumericCellValue);
                        v = dt.ToString("yyyy-MM-dd");
                    }
                    else
                    {
                        v = cell.ToString();
                    }
                    break;
                case CellType.Boolean:
                    v = Convert.ToString(cell.BooleanCellValue);
                    break;
                case CellType.Error:
                    v = ErrorEval.GetText(cell.ErrorCellValue);
                    break;
                default:
                    v = "";
                    break;
            }
            return v.Trim();
        }

        //从vipclass表中获取数据，初始化管理员和骨灰堂
        public void importParadiseUser()
        {
            Log("开始导入账号和骨灰堂信息 ...");
            
            tbUser currentUser = DB.tbUser.FirstOrDefault(m => m.id == user.id);
            string sql = "select * from vipclass where vipclass=2 order by zheng";
            SqlDataAdapter da = new SqlDataAdapter(sql, oldConnStr);
            DataTable dt = new DataTable();
            da.Fill(dt);
            string prevArea = "";
            int cnt = 1;
            StringBuilder sb = new StringBuilder();
            foreach (DataRow dr in dt.Rows)
            {
                string areaName = dr["zheng"].ToString().Trim();
                string name = dr["othername"].ToString().Trim();
                string fullName = dr["daiwei"].ToString().Trim();
                string userId = dr["username"].ToString().Trim();
                string pwd = dr["userpwd"].ToString().Trim();
                pwd = Md5.GetMD5String(pwd);   //加密
                if (areaName != prevArea) cnt = 1;
                prevArea = areaName;

                var dep = DB.tbArea.FirstOrDefault(m => m.name == areaName || m.name.IndexOf(areaName)>=0);
                if (dep == null) {
                    sb.AppendFormat("{0}镇不存在,{1}未创建！<br/>",areaName,name);
                    continue;
                }
                tbParadise p = DB.tbParadise.FirstOrDefault(m => m.name == name);
                if (p == null)
                {
                    p = new tbParadise
                    {
                        name = name,
                        fullName = fullName,
                        areacode = dep.code,
                        code = dep.scode + cnt,
                        type = "骨灰堂",
                        paradiseNum = 1,
                        pic = "/admin/images/paradise.jpg",
                        singles = 0,
                        doubles = 0,
                        multis = 0,
                        esingles = 0,
                        edoubles = 0,
                        emultis = 0,
                        graves = 0,
                        crdate = DateTime.Now
                    };
                }
                tbUser user = DB.tbUser.FirstOrDefault(m => m.userName == userId);
                if (user == null)
                {
                    user = new tbUser
                    {
                        userName = userId,
                        userPwd = pwd,
                        realName = userId,
                        able = true,
                        points = 0,
                        grade = 1,
                        addDate = DateTime.Now
                    };
                }

                tbUserParadise up = DB.tbUserParadise.FirstOrDefault(m => m.tbUser.userName == userId && m.tbParadise.name == name);
                if (up == null)
                {
                    DB.tbUserParadise.Add(new tbUserParadise
                    {
                        tbParadise = p,
                        tbUser = user,
                        addTime = DateTime.Now
                    });
                }
                cnt++;
                HttpContext.Current.Response.Write(".");
                HttpContext.Current.Response.Flush();
            }
            Log("开始保存信息...");
            try
            {
                DB.SaveChanges();
                Log("保存信息完成！");
            }
            catch (Exception ex)
            {
                Log("保存信息出错！"+ex.Message);
            }
            
            if (sb.Length > 0) {
                HttpContext.Current.Response.Write("错误信息：");
                HttpContext.Current.Response.Write(sb.ToString());
            }
            HttpContext.Current.Response.Flush();
            Log("导入账号和骨灰堂信息完成！");
        }
    }
}